package com.tripspins.app.dao;

import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

import com.tripspins.app.bo.Pin;
import com.tripspins.app.bo.Trip;
import com.tripspins.app.bo.User;
import com.tripspins.app.helper.TRIP_STATUS;

import java.util.ArrayList;

public class TripDAO {

    private SQLiteDatabase database;
    private PinDAO pinDao;



    public TripDAO(SQLiteDatabase database) {
        this.database = database;
    }


    public long persist(Trip trip) {
        if(trip.getId() == -1)
        {
            return insertTrip(trip);
        }
        else
        {
            return updateTrip(trip);
        }
    }

    private long updateTrip(Trip trip) {
        StringBuilder sbUpdate = new StringBuilder();
        sbUpdate.append("update trips " );
        sbUpdate.append("set txt_title  = ? ");
        sbUpdate.append( " , txt_status = ? ");
        sbUpdate.append( " , txt_synced = 'N' ");
        sbUpdate.append("where _id = ?");

        database.execSQL(sbUpdate.toString(),
                new String[]{trip.getDescription(),
                        trip.getStatus().getValue(),
                        String.valueOf(trip.getId())});

        pinDao = new PinDAO(database);
        pinDao.persist(trip.getId(), trip.getPins());

        return trip.getId();
    }

    public boolean isOnTheRoad() {
        String query = "select count(1) from Trips where txt_status = 'R';";
        Cursor cursor = database.rawQuery(query, null);
        cursor.moveToFirst();
        int count = cursor.getInt(0);
        cursor.close();
        if (count > 0)
            return true;
        return false;
    }

    public long insertTrip(Trip trip) {
        StringBuilder sbInsert = new StringBuilder();
        sbInsert.append("insert into trips ( txt_title" );
        sbInsert.append(", id_user ");
        sbInsert.append(", txt_status)");
        sbInsert.append(          " values ( ? " );
        sbInsert.append(                 " , ? ");
        sbInsert.append(                 " , ? );");

        database.execSQL(sbInsert.toString(),
                new String[]{trip.getDescription(),
                        String.valueOf(trip.getUserId()),
                        TRIP_STATUS.RUNNING.getValue()});

        long tripId = getLastInsertedID();

        pinDao = new PinDAO(database);
        pinDao.persist(tripId, trip.getPins());

        return tripId;
    }

    private long getLastInsertedID() {
        String query = "Select last_insert_rowid() FROM trips";
        Cursor cursor = database.rawQuery(query, null);
        cursor.moveToFirst();
        long id = cursor.getInt(0);
        cursor.close();
        return id;
    }

    public Trip getTrip(long id) {
        PinDAO pinDao = new PinDAO(database);
        StringBuilder sb = new StringBuilder();

        sb.append("Select trips._id ");
        sb.append(     ",trips.txt_title ");
        sb.append(     ",trips.txt_status ");
        sb.append(     ",trips.id_on_server ");
        sb.append(     ", trips.id_user ");
        sb.append("  From trips");
        sb.append(" Where trips._id = ?");


        Cursor cursorTrip = database.rawQuery(sb.toString(),
                new String[] { String.valueOf(id) });
        cursorTrip.moveToFirst();
        Trip trip = cursorToTrip(cursorTrip);
        cursorTrip.close();

        ArrayList<Pin> pins;
        pins = pinDao.getPins(id);
        trip.setPins(pins);
        return trip;
    }

    public Trip getTrip() {
        PinDAO pinDao = new PinDAO(database);
        StringBuilder sb = new StringBuilder();

        sb.append("Select trips._id ");
        sb.append(     ",trips.txt_title ");
        sb.append(     ",trips.txt_status ");
        sb.append(     ",trips.id_on_server ");
        sb.append(     ", trips.id_user ");
        sb.append("  From trips");
        sb.append(" Where trips.txt_status = ?");

        Cursor cursorTrip = database.rawQuery(sb.toString(),
                new String[] { TRIP_STATUS.RUNNING.getValue() });
        cursorTrip.moveToFirst();
        if(cursorTrip.isAfterLast())
            return null;
        Trip trip = cursorToTrip(cursorTrip);
        cursorTrip.close();
        ArrayList<Pin> pins;
        pins = pinDao.getPins(trip.getId());
        trip.setPins(pins);
        return trip;
    }

    public ArrayList<Trip> getAllTrips() {

        StringBuilder sb = new StringBuilder();
        sb.append("Select trips._id ");
        sb.append(     ", trips.txt_title ");
        sb.append(     ", trips.txt_status ");
        sb.append(     ",trips.id_on_server ");
        sb.append(     ", trips.id_user ");
        sb.append("  From trips ");
        sb.append(" Order By _id desc ");
        Cursor cursorTrip = database.rawQuery(sb.toString(), new String[] {});
        cursorTrip.moveToFirst();
        ArrayList<Trip> trips = new ArrayList<Trip>();
        while(!cursorTrip.isAfterLast()){
            trips.add(cursorToTrip(cursorTrip));
            cursorTrip.moveToNext();
        }
        cursorTrip.close();

        PinDAO pinDAO = new PinDAO(database);
        for(Trip t: trips )
        {
            t.setPins(pinDAO.getPins(t.getId()));
        }
        return trips;

    }

    private Trip cursorToTrip(Cursor cursor) {
        Trip t = new Trip();
        t.setId(cursor.getLong(0));
        t.setTitle(cursor.getString(1));
        t.setStatus(cursor.getString(2));
        t.setIdOnServer(cursor.getLong(3));
        t.setUserId(cursor.getLong(4));
        return t;
    }

    public void updateUser(User user) {

        StringBuilder sbUpdate = new StringBuilder();
        sbUpdate.append(" update trips " );
        sbUpdate.append(" set id_user = ? ");
        sbUpdate.append(" where id_user is null or id_user <= 0");

        database.execSQL(sbUpdate.toString(),
                new String[]{String.valueOf(user.getId())});

    }

    public void updateSyncedTrip(Trip trip) {

        StringBuilder sbUpdate = new StringBuilder();
        sbUpdate.append("update trips " );
        sbUpdate.append("set id_on_server = ? ");
        sbUpdate.append(  ", txt_synced = 'Y' ");
        sbUpdate.append(" Where trips._id = ?");
        database.execSQL(sbUpdate.toString(),
                new String[]{String.valueOf(trip.getIdOnServer())
                            , String.valueOf(trip.getId())});
        PinDAO pinDao = new PinDAO(database);
        pinDao.persist(trip.getId(),trip.getPins());
    }

    public ArrayList<Trip> getTripsToSync(long userId) {
        StringBuilder sb = new StringBuilder();
        sb.append("Select trips._id ");
        sb.append(     ", trips.txt_title ");
        sb.append(     ", trips.txt_status ");
        sb.append(     ", trips.id_on_server ");
        sb.append(     ", trips.id_user ");
        sb.append("  From trips ");
        sb.append(" Where trips.txt_synced = 'N' ");
        sb.append("   and trips.id_user = ? ");
        sb.append(" Order By _id desc ");
        Cursor cursorTrip = database.rawQuery(sb.toString(), new String[] {String.valueOf(userId)});
        cursorTrip.moveToFirst();
        ArrayList<Trip> trips = new ArrayList<Trip>();
        while(!cursorTrip.isAfterLast()){
            trips.add(cursorToTrip(cursorTrip));
            cursorTrip.moveToNext();
        }
        cursorTrip.close();

        PinDAO pinDAO = new PinDAO(database);
        for(Trip t: trips )
        {
            t.setPins(pinDAO.getPinsToSync(t.getId()));
        }
        return trips;
    }

    public ArrayList<Trip> getTripsSynced(long id) {
        StringBuilder sb = new StringBuilder();
        sb.append("Select trips._id ");
        sb.append(     ", trips.txt_title ");
        sb.append(     ", trips.txt_status ");
        sb.append(     ", trips.id_on_server ");
        sb.append(     ", trips.id_user ");
        sb.append("  From trips ");
        sb.append(" Where trips.txt_synced = 'Y' ");
        sb.append("   and trips.id_user = ? ");
        sb.append(" Order By _id desc ");
        Cursor cursorTrip = database.rawQuery(sb.toString(), new String[] {String.valueOf(id)});
        cursorTrip.moveToFirst();
        ArrayList<Trip> trips = new ArrayList<Trip>();
        while(!cursorTrip.isAfterLast()){
            trips.add(cursorToTrip(cursorTrip));
            cursorTrip.moveToNext();
        }
        cursorTrip.close();

        PinDAO pinDAO = new PinDAO(database);
        for(Trip t: trips )
        {
            t.setPins(pinDAO.getPinsSynced(t.getId()));
        }
        return trips;
    }

    public ArrayList<Trip> getTripsByUser(long id) {
        StringBuilder sb = new StringBuilder();
        sb.append("Select trips._id ");
        sb.append(     ", trips.txt_title ");
        sb.append(     ", trips.txt_status ");
        sb.append(     ", trips.id_on_server ");
        sb.append(     ", trips.id_user ");
        sb.append("  From trips ");
        sb.append(" Where trips.id_user = ?");
        sb.append(" Order By _id desc ");
        Cursor cursorTrip = database.rawQuery(sb.toString(), new String[] {String.valueOf(id)});
        cursorTrip.moveToFirst();
        ArrayList<Trip> trips = new ArrayList<Trip>();
        while(!cursorTrip.isAfterLast()){
            trips.add(cursorToTrip(cursorTrip));
            cursorTrip.moveToNext();
        }
        cursorTrip.close();

        PinDAO pinDAO = new PinDAO(database);
        for(Trip t: trips )
        {
            t.setPins(pinDAO.getPins(t.getId()));
        }
        return trips;
    }

    public ArrayList<Trip> getAnonymousTrips() {
        StringBuilder sb = new StringBuilder();
        sb.append("Select trips._id ");
        sb.append(     ", trips.txt_title ");
        sb.append(     ", trips.txt_status ");
        sb.append(     ", trips.id_on_server ");
        sb.append(     ", trips.id_user ");
        sb.append("  From trips ");
        sb.append(" Where trips.id_user <= 0 ");
        sb.append(" Order By _id desc ");
        Cursor cursorTrip = database.rawQuery(sb.toString(), null);
        cursorTrip.moveToFirst();
        ArrayList<Trip> trips = new ArrayList<Trip>();
        while(!cursorTrip.isAfterLast()){
            trips.add(cursorToTrip(cursorTrip));
            cursorTrip.moveToNext();
        }
        cursorTrip.close();

        PinDAO pinDAO = new PinDAO(database);
        for(Trip t: trips )
        {
            t.setPins(pinDAO.getPins(t.getId()));
        }
        return trips;
    }

    public void finish(Trip trip) {

        StringBuilder sbUpdate = new StringBuilder();
        sbUpdate.append("update trips " );
        sbUpdate.append("set txt_title  = ? ");
        sbUpdate.append( " , txt_status = ? ");
        sbUpdate.append( " , txt_synced = 'N' ");
        sbUpdate.append("where _id = ?");

        database.execSQL(sbUpdate.toString(),
                new String[]{trip.getDescription(),
                        trip.getStatus().getValue(),
                        String.valueOf(trip.getId())});

    }
}
